<?php

  function getPagesWithTag($tag) {
    $return_string = "";
   
    $query = "SELECT id FROM Tag WHERE text = '" . $tag . "'";
    $result = mysql_query($query);
    $tag_id = -1;
    if (mysql_num_rows($result) == 0) {
      $return_string .= "<returncode>0</returncode>"
                      . "<errormessage>No such tag!</errormessage>";
      return $return_string;
    } else {
      $result_row = mysql_fetch_assoc($result);
      $tag_id = $result_row['id'];
    }

    $return_string .= "<returncode>1</returncode>";

    $query = "SELECT WebPage.url, WebPageQuery.query, WebPageQuery.title, "
           . "Rating.total_rating, WebPageQuery.id AS webpagequery_id "
           . "FROM WebPageTag JOIN WebPageQuery "
           . "     ON WebPageTag.webpagequery_id=WebPageQuery.id "
           . "JOIN WebPage ON WebPageQuery.webpage_id = WebPage.id "
           . "LEFT JOIN (SELECT webpagequery_id, SUM(rating) AS total_rating "
           . "      FROM WebPageTagRating WHERE tag_id=$tag_id "
           . "      GROUP BY webpagequery_id) AS Rating "
           . "     ON WebPageQuery.id=Rating.webpagequery_id "
           . "WHERE WebPageTag.tag_id=$tag_id";
    $result = mysql_query($query);
   
    $return_string .= "<tag>$tag</tag>"; 
    while($result_row = mysql_fetch_assoc($result)) {
      if ($result_row['total_rating'] == null)
        $result_row['total_rating'] = 0;
      if ($result_row['title'] == null)
        $result_row['title'] = $result_row['url'];
      $query2 = "SELECT (ROUND(AVG(rating),2)) AS avgRating "
              . "FROM WebPageRating "
              . "WHERE webpagequery_id=" . $result_row['webpagequery_id'];
      $result2 = mysql_query($query2);  
      $result_row2 = mysql_fetch_assoc($result2);
      $return_string .= "<page>"
                      . "<url>" . htmlentities($result_row['url']) . "</url>"
                      . "<url_param>" . htmlentities($result_row['query']) 
                      . "</url_param>"
                      . "<title>" . $result_row['title'] . "</title>"
                      . "<tag_rating>" . $result_row['total_rating'] 
                      . "</tag_rating>"
                      . "<page_rating>" . $result_row2['avgRating']
                      . "</page_rating>"
                      . "</page>";
    }
    return $return_string;
  }

?>
